﻿Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports BusinessRule.IBusiness
Imports DatabaseAccess.IDAL
Imports System.Data
Imports BusinessRule.DTO
Imports BusinessRule.Common
Imports DatabaseAccess.DALFactory
Imports External.APICalling
Imports Camstar.WebClient.WebServicesProxy.InSiteWebServices
Imports DatabaseAccess.DBUtility

Public Class uMESCommonBusiness
    Implements uMESCommonIBusiness
    Private dal As IOracleDAL = uMESDalContext.instance.GetDalObject()


#Region "获取当前登录用户信息"
    Function GetCurrentUserInfo(ByVal strContainerName As String) As uMESProcessResult Implements uMESCommonIBusiness.GetCurrentUserInfo
        '添加用户的shift查询 modify tianFW 2017年11月24日
        Dim strQuery As String = "SELECT e.employeename,e.password,e.employeeid,e.fullname,f.factoryname,f.factoryid,f.description AS FactoryEName,wc.workcentername,wc.workcenterID,o.operationname" & vbCrLf & _
                               ",e.cardnum,r.resourcename,e.paycard,eroles.rolenames,e.shiftid," & vbCrLf & _
                               "te.teamid,te.teamname " & vbCrLf & _
                               "FROM employee e" & vbCrLf & _
                               "LEFT JOIN sessionvalues sv ON sv.sessionvaluesid = e.sessionvaluesid" & vbCrLf & _
                               "LEFT JOIN factory f ON f.factoryid = sv.factoryid" & vbCrLf & _
                               "LEFT JOIN workcenter wc ON wc.workcenterid = sv.workcenterid" & vbCrLf & _
                               "LEFT JOIN operation o ON o.operationid = sv.operationid" & vbCrLf & _
                               "LEFT JOIN resourcedef r ON r.resourceid = e.resourceid" & vbCrLf & _
                               "LEFT JOIN (" & vbCrLf & _
                               "   SELECT e.employeeid,wm_concat(r.rolename) AS rolenames" & vbCrLf & _
                               "   FROM employeerole er" & vbCrLf & _
                               "   LEFT JOIN employee e ON e.employeeid = er.employeeid" & vbCrLf & _
                               "   LEFT JOIN roledef r ON r.roleid = er.roleid" & vbCrLf & _
                               "   GROUP BY e.employeeid" & vbCrLf & _
                               ") eroles ON eroles.employeeid = e.employeeid" & vbCrLf & _
                               "   LEFT JOIN (SELECT  t.teamname,t.teamid,te.employeeid FROM teamemployee te LEFT JOIN team t " & vbCrLf & _
                              " ON t.teamid = te.teamid  ) te " & vbCrLf & _
                               "ON te.employeeid = e.employeeid AND ROWNUM = 1" & vbCrLf & _
                               "WHERE LOWER(e.employeename) = LOWER('" & strContainerName & "')"


        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetCurrentUserInfo(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region
#Region "获取用户角色"
    Public Function GetUerRole(ByVal strUserName As String, ByVal strRoleName As String) As DataTable Implements uMESCommonIBusiness.GetUerRole
        Dim strSql As String = String.Format("select e.employeeid, e.Employeename, e.Fullname,r.rolename,wc.workcentername FROM Employee e " & vbCrLf & _
                               "left join employeerole er on er.employeeid=e.employeeid " & vbCrLf & _
                               "left join roledef r on r.roleid=er.roleid  " & vbCrLf & _
                               "LEFT JOIN sessionvalues sv on sv.sessionvaluesid = e.sessionvaluesid " & vbCrLf & _
                               "LEFT JOIN workcenter wc ON wc.workcenterid = sv.workcenterid " & vbCrLf & _
                               "where  r.rolename='{0}'", strRoleName)
        Dim DT As DataTable = dal.GetCurrentUserInfo(strSql)
        Return DT
    End Function
#End Region

#Region "获取机型列表"
    Public Function GetProductFamily() As DataTable Implements uMESCommonIBusiness.GetProductFamily
        Dim strSql As String = "select pf.productfamilyname,pf.productfamilyid from productfamily pf "
        Dim DT As DataTable = dal.GetCurrentUserInfo(strSql)
        Return DT
    End Function
#End Region

#Region "剔除工序名称中的零件图号"
    Function GetSpecNameWithoutProduct(ByVal strSpecName As String) As String Implements uMESCommonIBusiness.GetSpecNameWithoutProduct
        Try
            Dim arraySpecName As String() = strSpecName.Split("-")
            strSpecName = arraySpecName(arraySpecName.Length - 2).ToString & "-" & arraySpecName(arraySpecName.Length - 1).ToString
            Return strSpecName
        Catch ex As Exception
            Return strSpecName
        End Try
    End Function
#End Region

#Region "根据ProductName获取默认版本"
    Function GetProductInfoByName(ByVal strProductName As String) As uMESProcessResult Implements uMESCommonIBusiness.GetProductInfoByName
        Dim strQuery As String = "SELECT pb.productname,p.productrevision,p.description,p.productid" & vbCrLf & _
                                "FROM product p" & vbCrLf & _
                                "LEFT JOIN productbase pb ON pb.revofrcdid = p.productid" & vbCrLf & _
                                "WHERE pb.productname = '" & strProductName & "'"

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetProductInfoByName(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "根据Product revision获取WorkFlow revision"
    ''' <summary>
    ''' 根据Product及版本获取WorkFlow及版本
    ''' </summary>
    ''' <param name="product"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetWorkFlowByProduct(ByVal product As RevisionedObjectRef) As RevisionedObjectRef Implements uMESCommonIBusiness.GetWorkFlowByProduct
        Dim sb As New StringBuilder
        GetWorkFlowByProduct = New RevisionedObjectRef

        sb.AppendLine("select wf.workflowid,wf.workflowrevision,wfb.workflowname ")
        sb.AppendLine("from workflow wf")
        sb.AppendLine("left join workflowbase wfb on wfb.revofrcdid = wf.workflowid")
        sb.AppendLine("left join product p on p.workflowbaseid=wfb.workflowbaseid or p.workflowid=wf.workflowid")
        sb.AppendLine("left join productbase pb  on pb.productbaseid=p.productbaseid ")
        sb.AppendFormat("where pb.productname='{0}' and p.productrevision='{1}'", product.__name, product.__rev)

        Dim resultdt As DataTable = dal.StandardGetDataTable(sb.ToString)
        If resultdt.Rows.Count > 0 Then
            GetWorkFlowByProduct.__Id = resultdt.Rows(0)("workflowid")
            GetWorkFlowByProduct.__rev = resultdt.Rows(0)("workflowrevision")
            GetWorkFlowByProduct.__name = resultdt.Rows(0)("workflowname")
        End If
    End Function
#End Region
#Region "根据ID获取workflow信息"
    Function GetWorkflowInfoByID(ByVal strID As String) As uMESProcessResult Implements uMESCommonIBusiness.GetWorkflowInfoByID
        Dim strQuery As String = "SELECT w.factoryid,w.firststepid,w.documentsetid,w.workcenterid,w.productid,wb.workflowname,w.workflowrevision,w.description,w.workflowid FROM workflow w" & vbCrLf & _
                                "LEFT JOIN workflowbase wb ON wb.workflowbaseid = w.workflowbaseid" & vbCrLf & _
                                "WHERE wb.workflowid = '" & strID & "'"
        Dim result As New uMESProcessResult
        Try
            Dim DT As DataTable = dal.GetWorkFlowInfoByName(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try
        Return result
    End Function
#End Region

#Region "根据WorkFlowName获取默认版本"
    Function GetWorkFlowInfoByName(ByVal strWorkFlowName As String) As uMESProcessResult Implements uMESCommonIBusiness.GetWorkFlowInfoByName
        Dim strQuery As String = "SELECT wb.workflowname,w.workflowrevision,w.description,w.workflowid" & vbCrLf & _
                                "FROM workflow w" & vbCrLf & _
                                "LEFT JOIN workflowbase wb ON wb.revofrcdid = w.workflowid" & vbCrLf & _
                                "WHERE wb.workflowname = '" & strWorkFlowName & "'"

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetWorkFlowInfoByName(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "生成新批次号"
    Function GetNewContainerName(ByVal strFactoryEName As String, ByVal strContainerName As String, ByVal intCount As Integer) As String Implements uMESCommonIBusiness.GetNewContainerName

        Dim str0 As String = ""
        For i As Integer = 0 To intCount - 1
            str0 = str0 & "0"
        Next

        If strFactoryEName <> "" And strContainerName = "" Then

            Dim strS As String = strFactoryEName & "-" & Now.ToString("yyyyMM") & "-"
            Dim strQuery As String = "SELECT MAX(containername) AS containername" & vbCrLf & _
                                    "FROM container" & vbCrLf & _
                                    "WHERE containername LIKE '" & strS & "%'"


            Try
                Dim DT As DataTable = dal.GetMaxContainerName(strQuery)
                Dim strMax As String = DT.Rows(0)("ContainerName").ToString
                If strMax <> "" Then
                    Dim array As String() = strMax.Split("-")

                    Return strS & (Convert.ToInt32(array(2)) + 1).ToString(str0)
                Else
                    Return strS & Convert.ToInt32("1").ToString(str0)
                End If
            Catch ex As Exception
                Return ""
            End Try
        ElseIf strFactoryEName = "" And strContainerName <> "" Then
            Try
                Dim array As String() = strContainerName.Split("-")

                Return array(0) & "-" & array(1) & "-" & (Convert.ToInt32(array(2)) + 1).ToString(str0)
            Catch ex As Exception
                Return ""
            End Try
        Else
            Return ""
        End If
    End Function
    'zll 20151030 修改：更改流水卡命名规则
    Public Function GetNewContainerNameByJX(ByVal strPrefix As String) As String Implements uMESCommonIBusiness.GetNewContainerNameByJX

        Dim strQuery As String = "SELECT MAX(QualificationNum) AS containername" & vbCrLf & _
                              "FROM container" & vbCrLf & _
                              "WHERE containername LIKE '" & strPrefix & "%'"
        Dim DT As DataSet = dal.ExecuteQuery(strQuery)

        Try
            Dim intMax As Integer = 0
            For Each row As DataRow In DT.Tables(0).Rows
                Dim strContianerName As String = row("containerName").ToString().Trim()
                Try
                    Dim array As String = strContianerName.Replace(strPrefix, "").Substring(0, 2)
                    If Convert.ToInt32(array) > intMax Then
                        intMax = Convert.ToInt32(array)
                    End If
                Catch e As Exception
                End Try
            Next

            intMax = intMax + 1

            GetNewContainerNameByJX = strPrefix & intMax.ToString("D2")
        Catch ex As Exception
            GetNewContainerNameByJX = ""
        End Try
    End Function

    Public Function GetNewChildIndexByJX(ByVal strPrefix As String) As Integer Implements uMESCommonIBusiness.GetNewChildIndexByJX
        Dim strQuery As String = "SELECT MAX(containername) AS containername" & vbCrLf & _
                                 "FROM container" & vbCrLf & _
                                 "WHERE containername LIKE '" & strPrefix & "%'"
        Dim DT As DataSet = dal.ExecuteQuery(strQuery)
        Dim intMax As Integer = 0
        For Each row As DataRow In DT.Tables(0).Rows
            Dim strContianerName As String = row("containerName").ToString().Trim()
            Try
                Dim array As String = strContianerName.Replace(strPrefix, "").Substring(0, 3)
                If Convert.ToInt32(array) > intMax Then
                    intMax = Convert.ToInt32(array)
                End If
            Catch e As Exception
            End Try
        Next
        GetNewChildIndexByJX = intMax
    End Function

#End Region

#Region "生成新虚拟批次号"
    Function GetNewXNContainerName(ByVal strMfgOrderName As String, ByVal strFlag As String, ByVal intCount As Integer) As String Implements uMESCommonIBusiness.GetNewXNContainerName

        Dim str0 As String = ""
        For i As Integer = 0 To intCount - 1
            str0 = str0 & "0"
        Next

        If strMfgOrderName = "" Then
            strMfgOrderName = "NoneMfgOrder"
        End If

        If strFlag = "" Then
            strFlag = "XN"
        End If

        If strMfgOrderName <> "" And strFlag <> "" Then

            Dim strS As String = strFlag & "-" & strMfgOrderName & "-"
            Dim strQuery As String = "SELECT MAX(togetherflag) AS togetherflag" & vbCrLf & _
                                    "FROM container" & vbCrLf & _
                                    "WHERE togetherflag LIKE '" & strS & "%'"


            Try
                Dim DT As DataTable = dal.GetMaxContainerName(strQuery)
                Dim strMax As String = DT.Rows(0)("TogetherFlag").ToString
                If strMax <> "" Then
                    Dim array As String() = strMax.Split("-")

                    Return strS & (Convert.ToInt32(array(array.Length - 1)) + 1).ToString(str0)
                Else
                    Return strS & Convert.ToInt32("1").ToString(str0)
                End If
            Catch ex As Exception
                Return ""
            End Try
        Else
            Return ""
        End If
    End Function
#End Region

#Region "生成新子批次号"
    Function GetNewMinorContainerName(ByVal strContainerName As String, ByVal intCount As Integer) As String Implements uMESCommonIBusiness.GetNewMinorContainerName
        Dim str0 As String = ""
        For i As Integer = 0 To intCount - 1
            str0 = str0 & "0"
        Next

        Dim strResult As String = ""
        If strContainerName <> "" Then
            Dim strS As String = strContainerName & "-P"
            Dim strQuery As String = "SELECT MAX(containername) AS containername" & vbCrLf & _
                                    "FROM container" & vbCrLf & _
                                    "WHERE containername LIKE '" & strS & "%'"


            Try
                Dim DT As DataTable = dal.GetMaxContainerName(strQuery)
                Dim strMax As String = DT.Rows(0)("ContainerName").ToString
                If strMax <> "" Then
                    Dim array As String() = strMax.Replace("P", "").Split("-")

                    strResult = strS & (Convert.ToInt32(array(array.Length - 1)) + 1).ToString(str0)
                Else
                    strResult = strS & Convert.ToInt32("1").ToString(str0)
                End If
            Catch ex As Exception
                strResult = ""
            End Try
        End If

        Return strResult
    End Function
#End Region

#Region "获取批次当前工序之前的所有工序（包括当前工序）"
    Function GetPassedSpecList(ByVal strContainerName As String) As uMESProcessResult Implements uMESCommonIBusiness.GetPassedSpecList
        Dim strQuery As String = "SELECT allspec.*" & vbCrLf & _
                                "FROM (" & vbCrLf & _
                                "   SELECT sl.Workflowstepid,sb2.Specname,s2.Specrevision,sl.Sequence,sl.Specid" & vbCrLf & _
                                "   FROM (" & vbCrLf & _
                                "       SELECT wfs1.workflowstepid,wfs1.sequence,wfs1.workflowid," & vbCrLf & _
                                "           (CASE WHEN wfs1.specid = '0000000000000000' THEN (SELECT sb1.revofrcdid FROM specbase sb1 WHERE sb1.specbaseid = wfs1.specbaseid) ELSE wfs1.specid END) AS specid" & vbCrLf & _
                                "       FROM workflowstep wfs1" & vbCrLf & _
                                "   ) sl" & vbCrLf & _
                                "   LEFT JOIN Spec s2 ON sl.Specid = s2.Specid" & vbCrLf & _
                                "   LEFT JOIN Specbase sb2 ON s2.Specbaseid = sb2.Specbaseid" & vbCrLf & _
                                "   WHERE sl.workflowid = (" & vbCrLf & _
                                "       SELECT wfs.workflowid" & vbCrLf & _
                                "       FROM workflowstep wfs" & vbCrLf & _
                                "       LEFT JOIN currentstatus cu ON cu.workflowstepid = wfs.workflowstepid" & vbCrLf & _
                                "       LEFT JOIN container c ON c.currentstatusid = cu.currentstatusid" & vbCrLf & _
                                "       WHERE c.containername = '" & strContainerName & "')" & vbCrLf & _
                                ") allSpec" & vbCrLf & _
                                "WHERE allSpec.sequence <= (" & vbCrLf & _
                                "   SELECT wfs.sequence" & vbCrLf & _
                                "   FROM workflowstep wfs" & vbCrLf & _
                                "   LEFT JOIN currentstatus cu ON cu.workflowstepid = wfs.workflowstepid" & vbCrLf & _
                                "   LEFT JOIN container c ON c.currentstatusid = cu.currentstatusid" & vbCrLf & _
                                "   WHERE c.containername = '" & strContainerName & "')" & vbCrLf & _
                                "ORDER BY allspec.sequence ASC" & vbCrLf

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetContainerInfo(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取批次工序列表"
    Public Function GetSpecByContainerName(ByVal strContainerName As String) As uMESProcessResult Implements uMESCommonIBusiness.GetSpecByContainerName
        Dim strSql As String = String.Format("SELECT allspec.*" & vbCrLf & _
                               "FROM ( " & vbCrLf & _
                               "SELECT sl.Workflowstepid,sb2.Specname,s2.Specrevision,sl.Sequence,sl.Specid " & vbCrLf & _
                               "FROM ( " & vbCrLf & _
                               "SELECT wfs1.workflowstepid,wfs1.sequence,wfs1.workflowid, " & vbCrLf & _
                               "(CASE WHEN wfs1.specid = '0000000000000000' THEN (SELECT sb1.revofrcdid FROM specbase sb1 WHERE sb1.specbaseid = wfs1.specbaseid) ELSE wfs1.specid END) AS specid " & vbCrLf & _
                               "FROM workflowstep wfs1 " & vbCrLf & _
                               ") sl " & vbCrLf & _
                               "LEFT JOIN Spec s2 ON sl.Specid = s2.Specid " & vbCrLf & _
                               "LEFT JOIN Specbase sb2 ON s2.Specbaseid = sb2.Specbaseid " & vbCrLf & _
                               "WHERE sl.workflowid = ( " & vbCrLf & _
                               "SELECT wfs.workflowid " & vbCrLf & _
                               "FROM workflowstep wfs " & vbCrLf & _
                               "LEFT JOIN currentstatus cu ON cu.workflowstepid = wfs.workflowstepid " & vbCrLf & _
                               "LEFT JOIN container c ON c.currentstatusid = cu.currentstatusid " & vbCrLf & _
                               "WHERE c.containername = '{0}') " & vbCrLf & _
                               ") allSpec " & vbCrLf & _
                               "ORDER BY allspec.sequence ASC ", strContainerName)

        Dim result As New uMESProcessResult
        Dim DT As DataTable = dal.GetContainerInfo(strSql)
        result.Result = True
        result.DBTable = DT
        Return result

    End Function
#End Region

#Region "获取检验备注信息"
    Function GetCheckNotesInfo(ByVal strContainerName As String) As uMESProcessResult Implements uMESCommonIBusiness.GetCheckNotesInfo
        Dim strQuery As String = "SELECT *" & vbCrLf & _
                                "FROM (" & vbCrLf & _
                                "   SELECT c.containername,sb.specname,e.fullname,cti.submitdate AS checkdate,cti.notes" & vbCrLf & _
                                "       ,DECODE(1,1,'巡检') AS checktype" & vbCrLf & _
                                "   FROM checktempinfo cti" & vbCrLf & _
                                "   LEFT JOIN container c ON c.containerid = cti.containerid" & vbCrLf & _
                                "   LEFT JOIN spec s ON s.specid = cti.specid" & vbCrLf & _
                                "   LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid" & vbCrLf & _
                                "   LEFT JOIN employee e ON e.employeeid = cti.submitemployeeid" & vbCrLf & _
                                "   WHERE c.containername = '" & strContainerName & "'" & vbCrLf & _
                                "   UNION ALL" & vbCrLf & _
                                "   SELECT c.containername,sb.specname,e.fullname,hml.txndate AS checkdate,cch.notes" & vbCrLf & _
                                "       ,DECODE(cch.checktype,1,'首件检验',2,'中件检验',3,'末件检验',4,'常规检验') AS checktype" & vbCrLf & _
                                "   FROM conventioncheckhistory cch" & vbCrLf & _
                                "   LEFT JOIN historymainline hml ON hml.historymainlineid = cch.historymainlineid" & vbCrLf & _
                                "   LEFT JOIN container c ON c.containerid = hml.containerid" & vbCrLf & _
                                "   LEFT JOIN workingreporthistory wrh ON wrh.workingreporthistoryid = cch.workingreportid" & vbCrLf & _
                                "   LEFT JOIN workcenterdispatching wcd ON wcd.workcenterdispatchingid = wrh.workcenterdispatchingid" & vbCrLf & _
                                "   LEFT JOIN spec s ON s.specid = wcd.specid" & vbCrLf & _
                                "   LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid" & vbCrLf & _
                                "   LEFT JOIN employee e ON e.employeeid = hml.employeeid" & vbCrLf & _
                                "   WHERE hml.reversalstatus = 1 AND c.containername = '" & strContainerName & "'" & vbCrLf & _
                                ")" & vbCrLf & _
                                "ORDER BY checkdate DESC" & vbCrLf

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetContainerInfo(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

    Function GetLYCaseContainerName(ByVal pix As String, ByVal strContainerName As String) As String Implements uMESCommonIBusiness.GetLYCaseContainerName

        Dim strTmp1 As String = pix + DateTime.Now.Year.ToString() + "-" + DateTime.Now.Month.ToString() + "-" + DateTime.Now.Day.ToString()

        Dim strQuery As String = String.Format("SELECT container.containername FROM container " &
                                               " WHERE parentcontainerid is null and containername LIKE '{0}%' " &
                                               "and  singlelmproductid is null ", strTmp1)
        Try
            Dim DT As DataTable = dal.GetMaxContainerName(strQuery)
            Dim intIndex As Integer = 0
            For Each row As DataRow In DT.Rows
                If row("ContainerName").ToString().Trim() = "" Then
                    Continue For
                End If
                Dim strNumber As String = row("ContainerName").ToString().Substring(strTmp1.Length)
                If intIndex < Convert.ToInt32(strNumber) Then
                    intIndex = Convert.ToInt32(strNumber)
                End If
            Next

            If strContainerName.Trim() <> "" Then
                Dim strNumber1 As String = strContainerName.Substring(strTmp1.Length)
                If Convert.ToInt32(strNumber1) > intIndex Then
                    intIndex = Convert.ToInt32(strNumber1)
                End If
            End If

            GetLYCaseContainerName = strTmp1 & (Convert.ToInt32(intIndex) + 1).ToString()
        Catch ex As Exception
            GetLYCaseContainerName = ""
        End Try
    End Function

    ''' <summary>
    ''' 根据班组获取员工号信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetEmpByTeam(ByVal para As Dictionary(Of String, String)) As DataTable Implements uMESCommonIBusiness.GetEmpByTeam
        Dim sb As StringBuilder = New StringBuilder()
        sb.AppendLine(" select distinct e.description,e.employeeid,e.fullname from employee e ")
        sb.AppendLine(" left join teamemployee te on te.employeeid = e.employeeid")
        sb.AppendLine(" left join team t on t.teamid = te.teamid ")
        sb.AppendLine("  left join sessionvalues sv on sv.sessionvaluesid = e.sessionvaluesid")
        sb.AppendLine("  left join workcenter wc on wc.workcenterid = sv.workcenterid ")
        sb.AppendLine(" where e.description is not null")
        If (para.ContainsKey("TEAMID")) Then
            If (para("TEAMID") <> "全部") Then
                sb.AppendFormat(" and t.teamname='{0}'", para("TEAMID"))
            End If
        End If

        If (para.ContainsKey("WorkCt")) Then
            If (para("WorkCt") <> "全部") Then
                sb.AppendFormat(" and wc.workcentername='{0}'", para("WorkCt"))
            End If
        End If

        sb.AppendLine(" order by e.description ")
        Dim EmpDt As DataTable = dal.StandardGetDataTable(sb.ToString())
        GetEmpByTeam = EmpDt
    End Function

#Region "增加属性"
    Public Function SaveUserAttribute(ByVal para As ContainerStartInfo) As uMESProcessResult Implements uMESCommonIBusiness.SaveUserAttribute

        Dim result As New uMESProcessResult()
        Dim strInfo As String = ""

        Dim m_AttrList As New Dictionary(Of String, List(Of ClientAPIEntity))

        Dim m_AttrChild As New List(Of ClientAPIEntity)

        Dim attEntity As New ClientAPIEntity
        attEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        attEntity.ClientInputTypeEnum = InputTypeEnum.Details
        attEntity.ClientDataName = "Name"
        attEntity.ClientDataValue = para.AttributeName
        m_AttrChild.Add(attEntity)

        attEntity = New ClientAPIEntity()
        attEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        attEntity.ClientInputTypeEnum = InputTypeEnum.Details
        attEntity.ClientDataName = "DataType"
        attEntity.ClientDataValue = "4"
        m_AttrChild.Add(attEntity)

        attEntity = New ClientAPIEntity()
        attEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        attEntity.ClientInputTypeEnum = InputTypeEnum.Details
        attEntity.ClientDataName = "AttributeValue"
        attEntity.ClientDataValue = para.attributeValue

        m_AttrChild.Add(attEntity)

        m_AttrList.Add("0", m_AttrChild)


        Dim m_DataList As New List(Of ClientAPIEntity)

        Dim dataEntity As New ClientAPIEntity

        Dim strSql As String = String.Format("SELECT c.containername,l.containerlevelname FROM CONTAINER c LEFT JOIN CONTAINERLEVEL l ON l.containerlevelid=c.levelid WHERE c.containername='{0}'", para.ContainerName)

        Dim DT As DataTable = dal.GetMaxContainerName(strSql)

        dataEntity.ClientDataTypeEnum = DataTypeEnum.ContainerField
        dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
        dataEntity.ClientDataName = "Container"
        dataEntity.ClientDataValue = para.ContainerName
        dataEntity.ClientOtherValue = DT.Rows(0)("containerlevelname").ToString().Trim()
        m_DataList.Add(dataEntity)

        Dim daServerHost As ServerHostInfo = para.ServerHostInfo
        'Dim serverHost As String = daServerHost.ServerHost
        'Dim serverPort As String = daServerHost.ServerPort
        Dim serverUser As String = daServerHost.UserName
        Dim serverPassword As String = daServerHost.Password

        Dim api As New CamstarClientAPI(serverUser, serverPassword)

        Dim success As Boolean = api.RunTxnServiceHasSubentity("ContainerAttrMaintDoc", "ContainerAttrMaint", m_DataList, "ServiceDetails", m_AttrList, strInfo)
        result.Result = success
        result.Message = strInfo
        Return result
    End Function

    Public Function SaveDenamicAttribute(ByVal paraList As List(Of ContainerStartInfo)) As uMESProcessResult

        Dim result As New uMESProcessResult()
        Dim strInfo As String = ""

        Dim m_AttrList As New Dictionary(Of String, List(Of ClientAPIEntity))

        For i As Integer = 0 To paraList.Count - 1
            Dim m_AttrChild As New List(Of ClientAPIEntity)

            Dim attEntity As New ClientAPIEntity
            attEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            attEntity.ClientInputTypeEnum = InputTypeEnum.Details
            attEntity.ClientDataName = "Name"
            attEntity.ClientDataValue = paraList(i).AttributeName
            m_AttrChild.Add(attEntity)

            attEntity = New ClientAPIEntity()
            attEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            attEntity.ClientInputTypeEnum = InputTypeEnum.Details
            attEntity.ClientDataName = "DataType"
            attEntity.ClientDataValue = "4"
            m_AttrChild.Add(attEntity)

            attEntity = New ClientAPIEntity()
            attEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            attEntity.ClientInputTypeEnum = InputTypeEnum.Details
            attEntity.ClientDataName = "AttributeValue"
            attEntity.ClientDataValue = paraList(i).attributeValue
            m_AttrChild.Add(attEntity)

            m_AttrList.Add(i.ToString().Trim(), m_AttrChild)
        Next

        Dim m_DataList As New List(Of ClientAPIEntity)

        Dim dataEntity As New ClientAPIEntity

        dataEntity.ClientDataTypeEnum = DataTypeEnum.ContainerField
        dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
        dataEntity.ClientDataName = "Container"
        dataEntity.ClientDataValue = paraList(0).ContainerName
        dataEntity.ClientOtherValue = paraList(0).Level
        m_DataList.Add(dataEntity)
        Dim daServerHost As ServerHostInfo = paraList(0).ServerHostInfo
        Dim serverHost As String = daServerHost.ServerHost
        Dim serverPort As String = daServerHost.ServerPort
        Dim serverUser As String = daServerHost.UserName
        Dim serverPassword As String = daServerHost.Password

        Dim api As New CamstarClientAPI(serverUser, serverPassword)

        Dim success As Boolean = api.RunTxnServiceHasSubentity("ContainerAttrMaintDoc", "ContainerAttrMaint", m_DataList, "ServiceDetails", m_AttrList, strInfo)
        result.Result = success
        result.Message = strInfo
        Return result
    End Function

    Public Function SetUserAttribute(ByVal para As ContainerStartInfo) As uMESProcessResult Implements uMESCommonIBusiness.SetUserAttribute

        Dim result As uMESProcessResult = New uMESProcessResult()

        'Dim strSql As String = String.Format("SELECT c.containerid,c.containername,t.userattributeid,t.attributevalue FROM userattribute  t " & vbCrLf & _
        '                      "LEFT JOIN CONTAINER c ON c.containerid=t.parentid " & vbCrLf & _
        '                      "WHERE t.userattributename='currSpecStatus'AND c.containername='{0}'", para.ContainerName)


        Dim strSql As String = String.Format("SELECT c.containerid,c.containername,t.userattributeid,t.attributevalue,t.userattributename FROM userattribute  t " & vbCrLf & _
                              "LEFT JOIN CONTAINER c ON c.containerid=t.parentid " & vbCrLf & _
                              "WHERE  c.containername='{0}'", para.ContainerName)

        Dim DT As DataTable = dal.StandardGetDataTable(strSql)

        If DT.Rows.Count = 0 Then
            result = SaveUserAttribute(para)
        Else

            Dim strFilter As String = String.Format("userattributename='{0}'", para.AttributeName)
            Dim tmpRow As DataRow() = DT.Select(strFilter)
            If tmpRow.Length = 0 Then

                Dim attList As List(Of ContainerStartInfo) = New List(Of ContainerStartInfo)

                For Each row As DataRow In DT.Rows
                    Dim tmpEnity As ContainerStartInfo = New ContainerStartInfo()
                    tmpEnity.AttributeName = row("userattributename").ToString().Trim()
                    tmpEnity.attributeValue = row("attributevalue").ToString().Trim()
                    tmpEnity.ContainerName = para.ContainerName
                    tmpEnity.Level = para.Level
                    tmpEnity.ServerHostInfo = para.ServerHostInfo
                    attList.Add(tmpEnity)
                Next
                Dim tmp1Enity As ContainerStartInfo = New ContainerStartInfo()
                tmp1Enity.AttributeName = para.AttributeName
                tmp1Enity.attributeValue = para.attributeValue
                tmp1Enity.ContainerName = para.ContainerName
                tmp1Enity.Level = para.Level
                tmp1Enity.ServerHostInfo = para.ServerHostInfo
                attList.Add(tmp1Enity)

                Dim result1 As uMESProcessResult = SaveDenamicAttribute(attList)
                result.Result = result1.Result
                If result1.Result = False Then
                    result.Message = result1.Message
                End If
            Else
                strSql = String.Format("UPDATE userattribute t SET t.attributevalue='{0}' WHERE t.userattributeid='{1}'", para.attributeValue, tmpRow(0)("userattributeid").ToString().Trim())
                dal.ExecuteSql(strSql)
                result.Result = True
            End If
        End If
        Return result
    End Function
#End Region

#Region "获得工段人员"
    Public Function GetEmpByWorkCenter(ByVal strWorkCenter As String) As uMESProcessResult Implements uMESCommonIBusiness.GetEmpByWorkCenter
        Dim strSql As String = String.Format("SELECT w.workcentername,e.employeename,e.fullname FROM workcenter w " & vbCrLf & _
                                            "LEFT JOIN sessionvalues se ON se.workcenterid=w.workcenterid " & vbCrLf & _
                                            "LEFT JOIN employee e ON e.employeeid=se.employeeid " & vbCrLf & _
                                            "WHERE w.workcentername='{0}'", strWorkCenter)
        Dim result As New uMESProcessResult
        Dim DT As DataTable = dal.GetContainerInfo(strSql)
        result.Result = True
        result.DBTable = DT
        Return result
    End Function
#End Region

#Region "获得人员列表"
    Public Function GetEmplyeeList(ByVal strFullname As String, ByVal strFactoryName As String) As uMESProcessResult Implements uMESCommonIBusiness.GetEmplyeeList
        Dim strSql As New StringBuilder
        strSql.AppendLine("SELECT e.employeename,e.fullname FROM employee e ")
        strSql.AppendLine("LEFT JOIN sessionvalues sv ON sv.employeeid=e.employeeid")
        strSql.AppendLine("LEFT JOIN factory f ON f.factoryid=sv.factoryid")
        strSql.AppendFormat("WHERE e.fullname like '%{0}%' and f.factoryname='{1}'", strFullname, strFactoryName)
        Dim result As New uMESProcessResult
        Dim DT As DataTable = dal.StandardGetDataTable(strSql.ToString)
        result.Result = True
        result.DBTable = DT
        Return result
    End Function
#End Region
    Public Function GetFactory() As DataTable Implements uMESCommonIBusiness.GetFactory
        Dim strSql As String = "select f.factoryname from factory f"
        Dim DT As DataTable = dal.StandardGetDataTable(strSql)
        Return DT
    End Function

    Public Function GetWorkCenter(ByVal strFactoryName As String) As DataTable Implements uMESCommonIBusiness.GetWorkCenter
        Dim strSql As String = String.Format("SELECT * FROM WORKCENTER w LEFT JOIN factory f ON f.factoryid=w.factoryid WHERE f.factoryname='{0}'", strFactoryName)
        Dim DT As DataTable = dal.StandardGetDataTable(strSql)
        Return DT
    End Function

    Public Function GetTeam(ByVal strWorkCenterName As String) As DataTable Implements uMESCommonIBusiness.GetTeam
        Dim strSql As String = String.Format("SELECT * FROM team t LEFT JOIN workcenter w ON w.workcenterid=t.workcenterid WHERE w.workcentername='{0}'", strWorkCenterName)
        Dim DT As DataTable = dal.StandardGetDataTable(strSql)
        Return DT
    End Function

    Public Function ReplaceContainerName(ByVal strContainerName As String) As String Implements uMESCommonIBusiness.ReplaceContainerName
        Dim strArray As String() = strContainerName.Split("%")
        If strArray.Length = 3 Then
            Dim strTmp As String = strArray(0) + "(" + strArray(1) + ")" + strArray(2)
            Return strTmp
        Else
            Return strContainerName
        End If
    End Function

#Region "得到工艺路线"
    Public Function GetWorkFlowSetp(ByVal strWorkflowName As String, ByVal strWorkFlowVersion As String) As DataTable Implements uMESCommonIBusiness.GetWorkFlowSetp
        Dim strSql As String = "SELECT wt.workflowstepid,wt.workflowstepname,wt.sequence,wb.workflowname,w.workflowrevision FROM workflowstep wt " & vbCrLf & _
                               "LEFT JOIN workflow w ON w.workflowid=wt.workflowid " & vbCrLf & _
                               "LEFT JOIN workflowbase wb ON wb.workflowbaseid=w.workflowbaseid " & vbCrLf & _
                               "WHERE wb.workflowname='" & strWorkflowName & "' AND w.workflowrevision='" & strWorkFlowVersion & "' ORDER BY wt.sequence"
        Dim DT As DataTable = dal.StandardGetDataTable(strSql)
        Return DT
    End Function
#End Region

#Region "获取关闭批次"
    Public Function GetCloseContainer(ByVal strContianerName As String) As DataTable Implements uMESCommonIBusiness.GetCloseContainer

        Dim strSQL As String = "SELECT  c.containername,c.qty,c.status,c.parentcontainerid,c.containerid,c.togetherflag," & vbCrLf & _
                               "pb.productname,p.productrevision,p.description ProductDesc,ue.attributevalue,ue.userattributename FROM container c " & vbCrLf & _
                               "LEFT JOIN product p on p.productid = c.productid " & vbCrLf & _
                               "LEFT JOIN productbase pb on pb.productbaseid = p.productbaseid " & vbCrLf & _
                               "LEFT JOIN Userattribute ue ON ue.parentid=c.containerid AND ue.userattributename='closeMemo' " & vbCrLf & _
                               "WHERE c.status=2 AND c.containerid<>c.containername and c.parentcontainerid is null "


        If strContianerName.Trim() <> "" Then
            strSQL += " AND  LOWER(c.containername) LIKE '%" & strContianerName.ToLower & "%' " & vbCrLf
        End If

        strSQL += " Order by c.ContainerName"

        Dim tmpTable As DataTable = dal.StandardGetDataTable(strSQL)

        Return tmpTable

    End Function

    ''' <summary>
    ''' 获取关闭批次 ‘add tianfw 2017-08-28
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetClostContaienr_new(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements IBusiness.uMESCommonIBusiness.GetClostContaienr_new
        Dim strsql As New StringBuilder
        Dim strSelect As String =
           <string>
                SELECT CU.PRODUCTNAME,CU.PRODUCTDESCRIPTION,C_.TOGETHERFLAG,SUBSTR(C_.TOGETHERFLAG, INSTR(C_.TOGETHERFLAG, '/') + 1) TOGETHERFLAGNAME,CU.PRODUCTID,C_.CONTAINERTYPE,
                DECODE(C_.CONTAINERTYPE,2,'批量加工',3,'批量带子序号',7,'单件加工') AS CONTAINERTYPENAME,CU.MFGORDERNAME,CU.WORKFLOWSTEPID,CU.WORKFLOWSTEPNAME,CU.WORKFLOWID,C_.ORIGINALFACTORYID FACTORYID
            </string>
        strsql.AppendLine("SELECT * FROM(")
        strsql.AppendLine(strSelect)
        strsql.AppendLine(",SUM(C_.QTY) AS WIPQTY ,'' CONTAINERID,''CONTAINERNAME,'' FaContianerID ")
        Dim selectStr As String =
           <string>
                FROM CONTAINER C_ 
                LEFT JOIN CONTAINERCURRENTINFO CU ON CU.CONTAINERID = C_.CONTAINERID
	            LEFT JOIN CURRENTSTATUS CS ON CS.CURRENTSTATUSID = C_.CURRENTSTATUSID
            </string>
        strsql.AppendLine(selectStr)
        Dim strcondition As New StringBuilder
        strcondition.AppendFormat(" WHERE CS.FACTORYID='{0}' AND C_.STATUS = 2 AND C_.CONTAINERID <> C_.CONTAINERNAME AND C_.PARENTCONTAINERID IS NULL AND C_.QTY>0", para("FactoryID"))
        strcondition.AppendLine("AND ((C_.CONTAINERCOMMENTS IS NOT NULL AND C_.CONTAINERCOMMENTS NOT LIKE '%(已被领料)%') ") ' AND C_.CONTAINERCOMMENTS LIKE '%变号为%' del 2018年3月14日 by tianFW
        strcondition.AppendLine("OR (C_.CONTAINERCOMMENTS IS NULL )) AND CU.CHANGECONTAINERID IS NULL")
        strcondition.AppendLine(" AND EXISTS ( SELECT 1 FROM CONTAINERSTATUSCHANGEHISTORY CY" +
              " LEFT JOIN CHANGESTATUSREASON CR ON CR.CHANGESTATUSREASONID = CY.CHANGESTATUSREASONID" +
              " WHERE CY.HISTORYID = C_.CONTAINERID AND CR.CHANGESTATUSREASONNAME='变号批次关闭原因' AND CY.status =2)")
        If para.Keys.Contains("ContainerName") Then
            strcondition.AppendFormat(" AND C_.CONTAINERNAME LIKE '%{0}%'", para("ContainerName"))
        End If
        If para.Keys.Contains("ContainerType") Then
            strcondition.AppendFormat(" AND C_.CONTAINERTYPE = '{0}'", para("ContainerType"))
        End If
        strcondition.AppendFormat(" AND CU.PRODUCTNAME<>'{0}'", para("ProductNO"))
        strsql.AppendLine(strcondition.ToString())
        strsql.AppendLine("AND C_.CONTAINERTYPE='7'")
        strsql.AppendLine(" GROUP BY CU.PRODUCTNAME,CU.PRODUCTDESCRIPTION,C_.TOGETHERFLAG,CU.PRODUCTID,C_.CONTAINERTYPE,")
        strsql.AppendLine("CU.MFGORDERNAME,CU.WORKFLOWSTEPID,CU.WORKFLOWSTEPNAME,CU.WORKFLOWID,C_.ORIGINALFACTORYID")
        strsql.AppendLine(" UNION ")
        strsql.AppendLine(strSelect)
        strsql.AppendLine(",C_.QTY AS WIPQTY ,C_.CONTAINERID,C_.CONTAINERNAME,C_.CONTAINERID AS FaContianerID")
        strsql.AppendLine(selectStr)
        strsql.AppendLine(strcondition.ToString())
        strsql.AppendLine("AND C_.CONTAINERTYPE<>'7'")
        strsql.AppendLine(") C ")
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strsql.ToString
        Dim strValue As String = ""
        strValue += "SELECT * FROM("
        strValue += strSelect
        strValue += ",C_.QTY,C_.CONTAINERID,C_.CONTAINERNAME"
        strValue += selectStr
        strValue += strcondition.ToString()
        strValue += ") C WHERE 1=1 "
        Return DatabaseHelper.GetContainerForPage(paraPage, strValue)
    End Function
#End Region

#Region "得到角色和工序对应关系"
    Public Function GetSpecAndRoleInfo(ByVal strRoleList As String) As DataTable Implements uMESCommonIBusiness.GetSpecAndRoleInfo

        Dim strRole As String = " "
        Dim strArray As String() = strRoleList.Split(",")
        For i As Integer = 0 To strArray.Length - 1
            strRole = strRole + "'" + strArray(i) + "',"
        Next

        If strRole.Trim() <> "" Then
            strRole = strRole.Substring(0, strRole.Length - 1)
        End If

        Dim strSql As String = "SELECT sb.specname,r.rolename FROM spec s " & vbCrLf & _
                               "LEFT JOIN specbase sb ON sb.specbaseid=s.specbaseid " & vbCrLf & _
                               "LEFT JOIN roledef r ON r.roleid=s.roleid " & vbCrLf & _
                               "WHERE r.rolename IN (" & strRole & ")"
        Dim DT As DataTable = dal.GetContainerInfo(strSql)
        Return DT
    End Function
#End Region

#Region "获取产品系列工艺路线BOM版本信息"
    Public Function GetProductByName(ByVal productname As String) As DataTable Implements uMESCommonIBusiness.GetProductByName
        Dim strSql As String = "SELECT p.productid,NVL(b.bomid,b1.bomid) bomid,NVL(wf.workflowid,wf1.workflowid) workflowid," & vbCrLf & _
                               "NVL(wf.workflowrevision,wf1.workflowrevision) workflowrevision,NVL(wb.workflowname,wb1.workflowname) workflowname,NVL(bb.bomname,bb1.bomname) bomname,NVL(b.bomrevision,b1.bomrevision) bomrevision from product p " & vbCrLf & _
                               "LEFT JOIN workflow wf ON wf.workflowid=p.workflowid" & vbCrLf & _
                               "LEFT JOIN workflowbase wb ON wb.workflowbaseid=wf.workflowbaseid" & vbCrLf & _
                               "LEFT JOIN workflowbase wb1 ON wb1.workflowbaseid=p.workflowbaseid " & vbCrLf & _
                               "LEFT JOIN workflow wf1 ON wf1.workflowid=wb1.revofrcdid  " & vbCrLf & _
                               "LEFT JOIN bom b ON b.bomid=p.bomid" & vbCrLf & _
                               "LEFT JOIN bombase bb ON bb.bombaseid=b.bombaseid " & vbCrLf & _
                               "LEFT JOIN bombase bb1 ON bb1.bombaseid=p.bombaseid" & vbCrLf & _
                               "LEFT JOIN bom b1 ON b1.bomid=bb1.revofrcdid " & vbCrLf & _
                               "LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid OR pb.revofrcdid=p.productid" & vbCrLf & _
                               "WHERE pb.productname='" & productname & "'"
        Dim DT As DataTable = dal.StandardGetDataTable(strSql)
        If DT.Rows.Count > 0 Then
            For i As Integer = 0 To DT.Rows.Count - 1
                If DT.Rows(i)("workflowname").ToString() <> "" Then
                    DT.Rows(i)("workflowname") += ("【" + DT.Rows(0)("workflowrevision") + "】")
                End If
                If DT.Rows(i)("bomname").ToString() <> "" Then
                    DT.Rows(i)("bomname") += ("【" + DT.Rows(0)("bomrevision") + "】")
                End If
            Next
        End If
        Return DT
    End Function
#End Region

#Region "获取所选产品版本的工艺路线BOM"
    Public Function GetWorkflowBom(ByVal productname As String, ByVal revision As String) As DataTable Implements uMESCommonIBusiness.GetWorkflowBom
        Dim strSql As String = "SELECT p.productid,NVL(b.bomid,b1.bomid) bomid,NVL(wf.workflowid,wf1.workflowid) workflowid," & vbCrLf & _
                               "NVL(wf.workflowrevision,wf1.workflowrevision) workflowrevision,NVL(wb.workflowname,wb1.workflowname) workflowname,NVL(bb.bomname,bb1.bomname) bomname,NVL(b.bomrevision,b1.bomrevision) bomrevision from product p " & vbCrLf & _
                               "LEFT JOIN workflow wf ON wf.workflowid=p.workflowid" & vbCrLf & _
                               "LEFT JOIN workflowbase wb ON wb.workflowbaseid=wf.workflowbaseid" & vbCrLf & _
                               "LEFT JOIN workflowbase wb1 ON wb1.workflowbaseid=p.workflowbaseid " & vbCrLf & _
                               "LEFT JOIN workflow wf1 ON wf1.workflowid=wb1.revofrcdid  " & vbCrLf & _
                               "LEFT JOIN bom b ON b.bomid=p.bomid" & vbCrLf & _
                               "LEFT JOIN bombase bb ON bb.bombaseid=b.bombaseid " & vbCrLf & _
                               "LEFT JOIN bombase bb1 ON bb1.bombaseid=p.bombaseid" & vbCrLf & _
                               "LEFT JOIN bom b1 ON b1.bomid=bb1.revofrcdid " & vbCrLf & _
                               "LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid OR pb.revofrcdid=p.productid" & vbCrLf & _
                               "WHERE pb.productname='" & productname & "' and p.productrevision='" & revision & "' "
        Dim DT As DataTable = dal.StandardGetDataTable(strSql)
        If DT.Rows.Count > 0 Then
            DT.Rows(0)("workflowname") += ("【" + DT.Rows(0)("workflowrevision") + "】")
            DT.Rows(0)("bomname") += ("【" + DT.Rows(0)("bomrevision") + "】")
        End If
        Return DT
    End Function
#End Region

#Region "获取工序名、号"
    Public Function GetWkFlStepname(ByVal workflowid As String) As DataTable Implements uMESCommonIBusiness.GetWkFlStepname
        workflowid = workflowid.Substring(0, 16)
        Dim strSql As String = "SELECT workflowstepname,islaststep,sequence,specbaseid,specid FROM workflowstep wf " & vbCrLf & _
                               " where workflowid='" & workflowid & "' order by sequence"
        Dim DT As DataTable = dal.StandardGetDataTable(strSql)
        DT.Columns.Add("workflowstepnameno")
        DT.Columns.Add("dclaststep")
        For i As Integer = 0 To DT.Rows.Count - 1
            Dim namestr As String = DT.Rows(i)("workflowstepname").ToString()
            Dim strArray As String() = namestr.Split("-")
            If strArray.Length >= 2 Then
                DT.Rows(i)("workflowstepnameno") = strArray(strArray.Length - 2)
                DT.Rows(i)("workflowstepname") = strArray(strArray.Length - 1)
            End If
            If DT.Rows(i)("islaststep").ToString() = "0" Then
                DT.Rows(i)("dclaststep") = " "
            Else
                DT.Rows(i)("dclaststep") = "是"
            End If
        Next
        Return DT
    End Function

    ''' <summary>
    ''' 获取工艺路线所属工艺列表 create tianFW 2017/09/11
    ''' </summary>Modify by YangJiang 20180614  S.SPECNO->,NVL(s.specno,SUBSTR(wt.workflowstepname,0,INSTR(wt.workflowstepname,'-','1')-1)) SPECNO
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetWorkFlowSetpsDetailByWorkflowID(ByVal para As Dictionary(Of String, String)) As System.Data.DataTable Implements uMESCommonIBusiness.GetWorkFlowSetpsDetailByWorkflowID
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT WT.WORKFLOWSTEPID, WT.WORKFLOWSTEPNAME, WT.SEQUENCE,WF.WORKFLOWID,WB.WORKFLOWNAME,S.SPECREVISION,WT.DESCRIPTION,f1.factoryname childwffactoryname,")
        sb.AppendLine("WF.WORKFLOWREVISION,S.SPECID,SB.SPECNAME,DECODE(S.SPECID,NULL,'SubWorkflow','Spec') WFSTYPE,WT.ISLASTSTEP,B.BOMID,f1.factoryid childwffactoryid,")
        sb.AppendLine("wc.workcentername,t.teamname,f.factoryname,s.factoryid,s.controlcode,s.iscooperation,s.isspotcheck,s.lastcheck") 'del ssd.durationperunit,wt.manhours by tianFW 删除工时和商品工时查询 2018年5月7日
        sb.AppendLine(",NVL(s.specno,SUBSTR(wt.workflowstepname,0,INSTR(wt.workflowstepname,'-','1')-1)) SPECNO") 'add YangJiang 20180614 查找工序号
        sb.AppendLine("FROM WORKFLOWSTEP WT")
        sb.AppendLine("LEFT JOIN WORKFLOWBASE WB_ ON WB_.WORKFLOWBASEID = WT.SUBWORKFLOWBASEID")
        sb.AppendLine("LEFT JOIN WORKFLOW WF ON WF.WORKFLOWID=NVL(WB_.REVOFRCDID,WT.SUBWORKFLOWID)")
        sb.AppendLine("LEFT JOIN WORKFLOWBASE WB ON WB.WORKFLOWBASEID = WF.WORKFLOWBASEID")
        sb.AppendLine("LEFT JOIN SPECBASE SB_ ON SB_.SPECBASEID = NVL(WT.SPECBASEID, WT.SUBWORKFLOWBASEID)")
        sb.AppendLine("LEFT JOIN SPEC S ON S.SPECID = NVL(SB_.REVOFRCDID,WT.SPECID)")
        sb.AppendLine("LEFT JOIN SPECBASE SB ON SB.SPECBASEID = S.SPECBASEID")
        sb.AppendLine("LEFT JOIN BOM B ON B.WORKFLOWID = WF.WORKFLOWID")
        sb.AppendLine("LEFT JOIN workcenter wc on wc.workcenterid = wt.workcenterid")
        sb.AppendLine("LEFT JOIN team t on t.teamid = wt.teamid")
        'sb.AppendLine("LEFT JOIN stepschedulingdetail ssd on ssd.stepid = wt.workflowstepid")
        sb.AppendLine("LEFT JOIN SPECBASE SB1 ON SB1.SPECBASEID = WT.SPECBASEID") 'modify 查询工序分厂 当前序对应的工艺的SPEC，上面的工艺可能是子工艺
        sb.AppendLine("LEFT JOIN SPEC S1 ON S1.SPECID = NVL(SB1.REVOFRCDID,WT.SPECID)")
        sb.AppendLine("LEFT JOIN factory f on f.factoryid = S1.factoryid")
        sb.AppendLine("LEFT JOIN factory f1 on f1.factoryid = wf.factoryid") 'add 查询子工艺分厂信息 tianFW 2018-4-26
        sb.AppendFormat(" WHERE WT.WORKFLOWID= '{0}'", para("WorkflowID"))
        If para.Keys.Contains("ChildWorkflow") Then
            sb.AppendLine("AND S.SPECID IS NULL")
        End If
        sb.AppendLine("ORDER BY WT.SEQUENCE")
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        dt.Columns.Add("workflowstepnameno")
        dt.Columns.Add("dclaststep")
        dt.Columns.Add("ChildWorkflow")
        dt.Columns.Add("stepType") '工序类型-固定外委、固定转工、检验工序、特殊过程
        Dim dtWorkflow As DataTable = GetWorkflowInfoByID(para("WorkflowID")).DBTable
        Dim strWorkflowFactory As String = para("userFactoryID") '如果工艺路线上没有定义工厂，就使用登录用户的工厂ID
        If dtWorkflow.Rows.Count > 0 Then
            If dtWorkflow.Rows(0)("factoryid").ToString() <> "" Then
                strWorkflowFactory = dtWorkflow.Rows(0)("factoryid").ToString() '获取工艺workflow上的工厂
            End If
        End If

        For i As Integer = 0 To dt.Rows.Count - 1
            Dim namestr As String = dt.Rows(i)("workflowstepname").ToString()
            Dim strArray As String() = namestr.Split("-")
            If strArray.Length >= 2 Then
                dt.Rows(i)("workflowstepnameno") = strArray(strArray.Length - 2)
                'dt.Rows(i)("workflowstepname") = strArray(strArray.Length - 1)'modify tianFW 2017/10/25 屏蔽
            End If
            If dt.Rows(i)("islaststep").ToString() = "0" Then
                dt.Rows(i)("dclaststep") = " "
            Else
                dt.Rows(i)("dclaststep") = "是"
            End If
            If dt.Rows(i)("WFSTYPE") = "SubWorkflow" Then
                dt.Rows(i)("ChildWorkflow") = "是"
            End If
            dt.Rows(i)("steptype") = "普通工序"
            If dt.Rows(i)("controlcode").ToString() = "LY05" Then
                dt.Rows(i)("stepType") = "固定外委"
            ElseIf dt.Rows(i)("controlcode").ToString() = "LY07" Then
                dt.Rows(i)("stepType") = "虚拟工序"
            ElseIf dt.Rows(i)("factoryid").ToString() <> strWorkflowFactory Then
                'modify by tianFW 2018年6月11日如果分厂属性为空，则显示 未维护分厂属性
                If dt.Rows(i)("factoryid").ToString() = "" Then
                    If dt.Rows(i)("childwffactoryid").ToString() <> strWorkflowFactory Then
                        If dt.Rows(i)("childwffactoryid").ToString() = "" Then
                            dt.Rows(i)("stepType") = "未维护分厂属性"
                        Else
                            dt.Rows(i)("stepType") = "固定转工"
                        End If
                    End If
                Else
                    dt.Rows(i)("stepType") = "固定转工"
                End If
            End If
            If dt.Rows(i)("lastCheck").ToString() = "1" Then
                dt.Rows(i)("stepType") = dt.Rows(i)("stepType") & "|检序"
            End If
            If dt.Rows(i)("isspotcheck").ToString() = "1" Then
                dt.Rows(i)("stepType") = dt.Rows(i)("stepType") & "|特殊"
            End If
        Next
        Return dt
    End Function
#End Region

#Region "获取配套表BOM"
    Public Function GetBOM(ByVal bomid As String) As DataTable Implements uMESCommonIBusiness.GetBOM
        Dim strSql As String
        'Modify by tianFW 添加bom Notes查询以及BomName、StepInfoName SPECTYPENAME stepinfodesc查询 2018年4月23日
        'modify by tianFW 修改Spec信息的查询 添加s.specno查询 2018年6月4日 add p.description pdesc 2018年7月18日
        strSql = "SELECT pb.productname,p.productid,sb.specname,s.specno,ST.SPECTYPENAME,pb.productname||':'||p.productrevision productinfo,p.description pdesc," & vbCrLf & _
                "pm.qtyrequired,pm.qty2required,p.productrevision,B.NOTES,BB.BOMNAME,PM.STEPINFOID,SIOB.STEPINFONAME,PM.BIGSMALLPART,sio.description stepinfodesc" & vbCrLf & _
                "FROM productmateriallistitem pm" & vbCrLf & _
                "LEFT JOIN productbase pb1 ON pb1.productbaseid=pm.productbaseid  " & vbCrLf & _
                "LEFT JOIN product p ON p.productid=NVL(pb1.revofrcdid,pm.productid) " & vbCrLf & _
                "LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid " & vbCrLf & _
                "LEFT JOIN specbase sb1 ON sb1.specbaseid=pm.specbaseid" & vbCrLf & _
                "LEFT JOIN spec s ON s.specid=NVL(sb1.revofrcdid,pm.specid) " & vbCrLf & _
                "LEFT JOIN specbase sb ON sb.specbaseid=s.specbaseid " & vbCrLf & _
                "LEFT JOIN BOM B ON B.BOMID = PM.BOMID" & vbCrLf & _
                "LEFT JOIN BOMBASE BB ON BB.BOMBASEID = B.BOMBASEID" & vbCrLf & _
                "LEFT JOIN STEPINFO SIO ON SIO.STEPINFOID = PM.STEPINFOID" & vbCrLf & _
                "LEFT JOIN STEPINFOBASE SIOB ON SIOB.STEPINFOBASEID = SIO.STEPINFOBASEID" & vbCrLf & _
                "LEFT JOIN SPECTYPE ST ON ST.SPECTYPEID = SIO.SPECTYPEID " & vbCrLf & _
                "WHERE pm.bomid='" & bomid & "'"

        Dim DT As DataTable = dal.StandardGetDataTable(strSql)
        DT.Columns.Add("specnameno")
        'DT.Columns.Add("productinfo")‘屏蔽 tianFW 2018年6月4日
        For i As Integer = 0 To DT.Rows.Count - 1
            If Not DT.Rows(i).IsNull("specno") Then
                DT.Rows(i)("specnameno") = DT.Rows(i)("specno")
            Else
                Dim namestr As String = DT.Rows(i)("specname").ToString()
                Dim strArray As String() = namestr.Split("-")
                If strArray.Length >= 2 Then
                    'DT.Rows(i)("specname") = strArray(strArray.Length - 1)'modify tianFW 2017/10/25 屏蔽
                    DT.Rows(i)("specnameno") = Convert.ToInt32(strArray(strArray.Length - 2))
                End If
            End If
            'DT.Rows(i)("productinfo") = DT.Rows(i)("productname") + ":" + DT.Rows(i)("productrevision") modify tianFW 2018/06/04 屏蔽
        Next
        Dim tmpRows As DataRow() = DT.Select("", "specnameno asc")
        Dim dtq As DataTable = DT.Clone()
        For Each row1 As DataRow In tmpRows
            dtq.ImportRow(row1)
        Next
        Return dtq
    End Function
#End Region

#Region "获取检测项"
    Public Function GetCheckPointnote(ByVal workflowid As String) As DataTable Implements uMESCommonIBusiness.GetCheckPointnote
        Dim strSql As String
        strSql = "SELECT ckp.checkpointsid,ckp.checkpointnote,wf.workflowstepname,wf.sequence,sb.specname FROM Workflowstep wf  " & vbCrLf & _
                               "LEFT JOIN spec s ON s.specid=wf.specid" & vbCrLf & _
                               "LEFT JOIN specbase sb ON sb.specbaseid=s.specbaseid " & vbCrLf & _
                               "INNER JOIN checkpointgroup chkpg ON chkpg.specid=s.specid" & vbCrLf & _
                               "LEFT JOIN Checkpoints ckp ON ckp.checkpointgroupid=chkpg.checkpointgroupid " & vbCrLf & _
                               "WHERE wf.workflowid='" & workflowid & "' " & vbCrLf & _
                                "UNION" & vbCrLf & _
                                "SELECT ckp.checkpointsid,ckp.checkpointnote,wf.workflowstepname,wf.sequence,sb.specname FROM Workflowstep wf  " & vbCrLf & _
                               "LEFT JOIN specbase sb ON sb.specbaseid= wf.specbaseid" & vbCrLf & _
                               "LEFT JOIN spec s ON s.specid=sb.revofrcdid " & vbCrLf & _
                               "INNER JOIN checkpointgroup chkpg ON chkpg.specid=s.specid " & vbCrLf & _
                               "LEFT JOIN Checkpoints ckp ON ckp.checkpointgroupid=chkpg.checkpointgroupid" & vbCrLf & _
                               "WHERE wf.workflowid='" & workflowid & "' " & vbCrLf & "ORDER BY sequence"


        Dim DT As DataTable = dal.StandardGetDataTable(strSql)
        DT.Columns.Add("workflowstepnameno")
        For i As Integer = 0 To DT.Rows.Count - 1
            Dim namestr As String = DT.Rows(i)("specname").ToString()
            Dim strArray As String() = namestr.Split("-")
            If strArray.Length >= 2 Then
                DT.Rows(i)("specname") = strArray(strArray.Length - 1)
            End If
            namestr = DT.Rows(i)("workflowstepname").ToString()
            strArray = namestr.Split("-")
            If strArray.Length >= 2 Then
                DT.Rows(i)("workflowstepnameno") = strArray(strArray.Length - 2)
                DT.Rows(i)("workflowstepname") = strArray(strArray.Length - 1)
            End If
        Next
        Return DT
    End Function
#End Region

#Region "获取所选产品版本的工艺路线信息"
    Public Function GetWorkflowBomByProduct(ByVal productname As String, ByVal revision As String) As DataTable Implements uMESCommonIBusiness.GetWorkflowBomByProduct

        Dim strSql As New StringBuilder
        strSql.AppendLine("SELECT pb.productname,p.productrevision,wf1.workflowid,decode(wb1.workflowname,'','',wb1.workflowname||':'||wf1.workflowrevision) workflowname ")
        strSql.AppendLine(" FROM product p ")
        strSql.AppendLine("LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid ")
        strSql.AppendLine("LEFT JOIN workflow wf ON wf.workflowid=p.workflowid ")
        strSql.AppendLine("LEFT JOIN workflowbase wb ON wb.workflowbaseid=p.workflowbaseid ")
        strSql.AppendLine("LEFT JOIN workflow wf1 ON wf1.workflowid=NVL(wb.revofrcdid,wf.workflowid) ")
        strSql.AppendLine("LEFT JOIN workflowbase wb1 ON wb1.workflowbaseid=wf1.workflowbaseid ")
        strSql.AppendFormat("WHERE pb.productname='{0}'  AND p.productrevision='{1}'", productname, revision)

        Dim DT As DataTable = dal.StandardGetDataTable(strSql.ToString)
        Return DT
    End Function
#End Region

#Region "获取所选产品版本的BOM信息"
    Public Function GetBomByProduct(ByVal productname As String, ByVal revision As String) As DataTable Implements uMESCommonIBusiness.GetBomByProduct

        Dim strSql As New StringBuilder
        strSql.AppendLine("SELECT pb.productname,p.productrevision,b1.bomid,decode(bb1.bomname,'','',bb1.bomname||':'||b1.bomrevision) bomname  FROM product p ")
        strSql.AppendLine("LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid ")
        strSql.AppendLine("LEFT JOIN bom b ON b.bomid=p.bomid ")
        strSql.AppendLine("LEFT JOIN bombase bb ON bb.bombaseid=p.bombaseid ")
        strSql.AppendLine("LEFT JOIN bom b1 ON b1.bomid=NVL(bb.revofrcdid,b.bomid) ")
        strSql.AppendLine("LEFT JOIN bombase bb1 ON bb1.bombaseid=b1.bombaseid ")
        strSql.AppendFormat("WHERE pb.productname='{0}'  AND p.productrevision='{1}'", productname, revision)

        Dim DT As DataTable = dal.StandardGetDataTable(strSql.ToString)
        Return DT
    End Function

    ''' <summary>
    ''' 获取工艺下面的bom信息 create tianFW 2017年12月4日
    ''' </summary>
    ''' <param name="workflowID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetBomByWorkflowID(ByVal workflowID As String) As DataTable Implements uMESCommonIBusiness.GetBomByWorkflowID
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT B.BOMID,B.WIPMSGDEFMGRID,B.BOMBASEID,B.WORKFLOWID,B.PRODUCTID,DECODE(BB.BOMNAME,'','',BB.BOMNAME||':'||B.BOMREVISION) BOMNAME")
        strsql.AppendLine(",DECODE(BT.BILLTYPENAME,'消耗件','BOM','大小件目录','PRODUCTMATERIAL','BOM') BILLTYPENAME")
        strsql.AppendLine("FROM BOM B")
        strsql.AppendLine("LEFT JOIN BOMBASE BB ON BB.BOMBASEID = B.BOMBASEID")
        strsql.AppendLine("LEFT JOIN BILLTYPE BT ON BT.BILLTYPEID = B.BILLTYPEID")
        strsql.AppendFormat("WHERE B.WORKFLOWID ='{0}'", workflowID)
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
    End Function

    ''' <summary>
    ''' 获取件号下面的bom信息 create tianFW 2017年12月5日
    ''' </summary>
    ''' <param name="ProductID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetBomByProductID(ByVal ProductID As String) As DataTable Implements uMESCommonIBusiness.GetBomByProductID
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT B.BOMID,B.WIPMSGDEFMGRID,B.BOMBASEID,B.WORKFLOWID,B.PRODUCTID,DECODE(BB.BOMNAME,'','',BB.BOMNAME||':'||B.BOMREVISION) BOMNAME")
        strsql.AppendLine("FROM BOM B")
        strsql.AppendLine("LEFT JOIN BOMBASE BB ON BB.BOMBASEID = B.BOMBASEID")
        strsql.AppendFormat("WHERE B.PRODUCTID ='{0}'", ProductID)
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
    End Function
#End Region

#Region "获取所选产品版本的材质信息"
    Public Function GetMaterialByProduct(ByVal productname As String, ByVal revision As String) As DataTable Implements uMESCommonIBusiness.GetMaterialByProduct
        Dim strSql As String = "SELECT MaterialStatus,MaterialSize,MaterialSpecification,MaterialTrademark,Materialcode FROM product p" & vbCrLf & _
                               "LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid OR pb.revofrcdid=p.productid" & vbCrLf & _
                               "WHERE pb.productname='" & productname & "' and p.productrevision='" & revision & "' "
        Dim DT As DataTable = dal.StandardGetDataTable(strSql)

        Return DT
    End Function
#End Region

    ''' <summary>
    ''' 根据产品名称获取产品列表
    ''' </summary>
    ''' <param name="strProductName"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetProductInfoByName(ByVal strProductName As String, ByVal strProductRev As String) As System.Data.DataTable Implements IBusiness.uMESCommonIBusiness.GetProductInfoByName
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT P.*  FROM PRODUCT P ")
        sb.AppendLine("  LEFT JOIN PRODUCTBASE PB ON P.PRODUCTBASEID = PB.PRODUCTBASEID")
        If String.IsNullOrWhiteSpace(strProductRev) Then
            sb.AppendFormat(" WHERE PB.PRODUCTNAME = '{0}'  AND P.PRODUCTID = PB.REVOFRCDID", strProductName)
        Else
            sb.AppendFormat(" WHERE PB.PRODUCTNAME = '{0}'  AND P.PRODUCTREVISION = '{1}'", strProductName, strProductRev)
        End If

        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)

        If dt Is Nothing Then
            GetProductInfoByName = New DataTable
        Else
            GetProductInfoByName = dt
        End If
    End Function

    ''' <summary>
    ''' 根据产品ID获取工艺列表，包括子工艺，按车间过滤
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetWorkFlowWithSubByProductID(ByVal strProductID As String, ByVal strFactoryID As String) As System.Data.DataTable Implements IBusiness.uMESCommonIBusiness.GetWorkFlowWithSubByProductID
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT * ")
        sb.AppendLine(" FROM (SELECT T.WORKFLOWID,T.WORKFLOWBASEID,  WFB.WORKFLOWNAME || ':' || T.WORKFLOWREVISION || '(' || T.WORKFLOWTYPE || ')' WORKFLOWNAME,T.PRODUCTID, T.FACTORYID")
        sb.AppendLine("   FROM WORKFLOW T")
        sb.AppendLine("    LEFT JOIN WORKFLOWBASE WFB ON WFB.WORKFLOWBASEID = T.WORKFLOWBASEID ")
        sb.AppendLine("    UNION")
        sb.AppendLine("    SELECT WF.WORKFLOWID,WF.WORKFLOWBASEID ,WFB1.WORKFLOWNAME || ':' || WF.WORKFLOWREVISION || '(' || WF.WORKFLOWTYPE || ')' WORKFLOWNAME,T.PRODUCTID, WF.FACTORYID")
        sb.AppendLine("      FROM PRODUCT T")
        sb.AppendLine("      LEFT JOIN WORKFLOWBASE WFB   ON WFB.WORKFLOWBASEID = T.WORKFLOWBASEID")
        sb.AppendLine("      LEFT JOIN WORKFLOW WF    ON WF.WORKFLOWID = NVL(WFB.REVOFRCDID, T.WORKFLOWID)")
        sb.AppendLine("      LEFT JOIN WORKFLOWBASE WFB1     ON WFB1.WORKFLOWBASEID = WF.WORKFLOWBASEID ) T")
        sb.AppendFormat(" WHERE T.PRODUCTID = '{0}' AND T.FACTORYID='{1}'", strProductID, strFactoryID)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        If dt Is Nothing Then
            GetWorkFlowWithSubByProductID = New DataTable
        Else
            GetWorkFlowWithSubByProductID = dt
        End If
    End Function

    ''' <summary>
    ''' 获取工艺路线所属工艺列表
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetWorkFlowSetpsByWorkflowID(ByVal strWorkflowID As String) As System.Data.DataTable Implements IBusiness.uMESCommonIBusiness.GetWorkFlowSetpsByWorkflowID
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT WT.WORKFLOWSTEPID,WT.WORKFLOWSTEPNAME,WT.SEQUENCE   ")
        sb.AppendLine("FROM WORKFLOWSTEP WT")
        sb.AppendFormat(" WHERE WT.WORKFLOWID= '{0}'", strWorkflowID)
        sb.AppendLine("ORDER BY WT.SEQUENCE")
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)

        If dt Is Nothing Then
            GetWorkFlowSetpsByWorkflowID = New DataTable
        Else
            GetWorkFlowSetpsByWorkflowID = dt
        End If
    End Function

    ''' <summary>
    ''' 流水卡扫描
    ''' create:Wangjh 2017-9-18
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ContainerScan(ByVal containerID As String) As String Implements uMESCommonIBusiness.ContainerScan
        Dim strSql As String = String.Format(" SELECT containername,containertype,c.togetherflag FROM container c WHERE c.containerid='{0}'", containerID)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
        Dim containerName As String = ""
        If dt.Rows.Count = 0 Then
            ContainerScan = containerID
            Exit Function
        End If
        containerName = dt.Rows(0)("containername").ToString
        If dt.Rows(0)("containertype").ToString = "7" Then '单件单卡
            Dim arrContainerName As String() = containerName.Split("/")
            ContainerScan = String.Concat(arrContainerName.First, "/", arrContainerName.Last.Substring(0, 4))
        Else
            ContainerScan = containerName
        End If
    End Function
End Class
